
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Matchs_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Matchs_Insert]

IF OBJECT_ID(N'[dbo].[Matchs_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Matchs_Update]

IF OBJECT_ID(N'[dbo].[Matchs_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Matchs_Delete]

IF OBJECT_ID(N'[dbo].[Matchs_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Matchs_Select]

--endregion

GO


--region [dbo].[Matchs_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Matchs_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdMatch int = null,
	@IdRound int = null,
	@IdClubHome int = null,
	@IdClubVisitor int = null,
	@StartDate datetime = null,
	@Status nvarchar(50) = null,
	@Result nvarchar(10) = null,
	@Description nvarchar(1000) = null,
	@IsCare bit = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Matchs]
					WHERE
					(
						(@IdMatch is null OR [Matchs].[IdMatch] = @IdMatch)
						AND (@IdRound is null OR [Matchs].[IdRound] = @IdRound)
						AND (@IdClubHome is null OR [Matchs].[IdClubHome] = @IdClubHome)
						AND (@IdClubVisitor is null OR [Matchs].[IdClubVisitor] = @IdClubVisitor)
						AND (@StartDate is null OR [Matchs].[StartDate] = @StartDate)
						AND (@Status is null OR [Matchs].[Status] like @Status)
						AND (@Result is null OR [Matchs].[Result] like @Result)
						AND (@Description is null OR [Matchs].[Description] like @Description)
						AND (@IsCare is null OR [Matchs].[IsCare] = @IsCare)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Matchs].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 1 THEN [Matchs].[IdMatch] END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 0 THEN [Matchs].[IdMatch] END DESC,
						CASE WHEN @SortBy = 'IdRound' and @SortType = 1 THEN [Matchs].[IdRound] END ASC,
						CASE WHEN @SortBy = 'IdRound' and @SortType = 0 THEN [Matchs].[IdRound] END DESC,
						CASE WHEN @SortBy = 'IdClubHome' and @SortType = 1 THEN [Matchs].[IdClubHome] END ASC,
						CASE WHEN @SortBy = 'IdClubHome' and @SortType = 0 THEN [Matchs].[IdClubHome] END DESC,
						CASE WHEN @SortBy = 'IdClubVisitor' and @SortType = 1 THEN [Matchs].[IdClubVisitor] END ASC,
						CASE WHEN @SortBy = 'IdClubVisitor' and @SortType = 0 THEN [Matchs].[IdClubVisitor] END DESC,
						CASE WHEN @SortBy = 'StartDate' and @SortType = 1 THEN [Matchs].[StartDate] END ASC,
						CASE WHEN @SortBy = 'StartDate' and @SortType = 0 THEN [Matchs].[StartDate] END DESC,
						CASE WHEN @SortBy = 'Status' and @SortType = 1 THEN [Matchs].[Status] END ASC,
						CASE WHEN @SortBy = 'Status' and @SortType = 0 THEN [Matchs].[Status] END DESC,
						CASE WHEN @SortBy = 'Result' and @SortType = 1 THEN [Matchs].[Result] END ASC,
						CASE WHEN @SortBy = 'Result' and @SortType = 0 THEN [Matchs].[Result] END DESC,
						CASE WHEN @SortBy = 'Description' and @SortType = 1 THEN [Matchs].[Description] END ASC,
						CASE WHEN @SortBy = 'Description' and @SortType = 0 THEN [Matchs].[Description] END DESC,
						CASE WHEN @SortBy = 'IsCare' and @SortType = 1 THEN [Matchs].[IsCare] END ASC,
						CASE WHEN @SortBy = 'IsCare' and @SortType = 0 THEN [Matchs].[IsCare] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Matchs]
			Where 
			(
				(@IdMatch is null OR [Matchs].[IdMatch] = @IdMatch)
				AND (@IdRound is null OR [Matchs].[IdRound] = @IdRound)
				AND (@IdClubHome is null OR [Matchs].[IdClubHome] = @IdClubHome)
				AND (@IdClubVisitor is null OR [Matchs].[IdClubVisitor] = @IdClubVisitor)
				AND (@StartDate is null OR [Matchs].[StartDate] = @StartDate)
				AND (@Status is null OR [Matchs].[Status] like @Status)
				AND (@Result is null OR [Matchs].[Result] like @Result)
				AND (@Description is null OR [Matchs].[Description] like @Description)
				AND (@IsCare is null OR [Matchs].[IsCare] = @IsCare)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Matchs_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Matchs_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Matchs_Insert]
	@IdMatch int OUTPUT,
	@IdRound int,
	@IdClubHome int,
	@IdClubVisitor int,
	@StartDate datetime,
	@Status nvarchar(50),
	@Result nvarchar(10),
	@Description nvarchar(1000),
	@IsCare bit

AS


INSERT INTO [dbo].[Matchs] 
(
	[IdRound],
	[IdClubHome],
	[IdClubVisitor],
	[StartDate],
	[Status],
	[Result],
	[Description],
	[IsCare]
)
VALUES 
(
	@IdRound,
	@IdClubHome,
	@IdClubVisitor,
	@StartDate,
	@Status,
	@Result,
	@Description,
	@IsCare
)

SET @IdMatch = SCOPE_IDENTITY()

--end [dbo].[Matchs_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Matchs_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Matchs_Update]
	@IdMatch int,
	@IdRound int,
	@IdClubHome int,
	@IdClubVisitor int,
	@StartDate datetime,
	@Status nvarchar(50),
	@Result nvarchar(10),
	@Description nvarchar(1000),
	@IsCare bit
AS


UPDATE [dbo].[Matchs] SET
	[IdRound] = @IdRound,
	[IdClubHome] = @IdClubHome,
	[IdClubVisitor] = @IdClubVisitor,
	[StartDate] = @StartDate,
	[Status] = @Status,
	[Result] = @Result,
	[Description] = @Description,
	[IsCare] = @IsCare
WHERE
	[IdMatch] = @IdMatch

--end [dbo].[Matchs_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Matchs_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Matchs_Delete]
	@IdMatch int
AS


DELETE FROM [dbo].[Matchs]
WHERE
(
	[IdMatch] = @IdMatch
)

--end [dbo].[Matchs_Delete]
--endregion

GO
--=========================================================================================--

